package com.mapper;

import com.dto.NoteWithUserDTO;
import com.pojo.Notes;
import com.vo.NoteWithStatsVO;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface NotesMapper {

    @Insert("INSERT INTO notes(user_id, title, content, cover_image_url, is_private, created_at, updated_at) " +
            "VALUES(#{userId}, #{title}, #{content}, #{coverImageUrl}, #{isPrivate}, #{createdAt}, #{updatedAt})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(Notes note);


    @Select("SELECT user_id FROM notes WHERE id = #{noteId}")
    Integer selectAuthorIdByNoteId(Integer noteId);


    @Update("UPDATE notes SET " +
            "title = #{title}, " +
            "content = #{content}, " +
            "cover_image_url = #{coverImageUrl}, " +
            "is_private = #{isPrivate}, " +
            "updated_at = #{updatedAt} " +
            "WHERE id = #{id}")
    int update(Notes note);

    @Delete("DELETE FROM notes WHERE id = #{id}")
    int delete(Integer id);

    @Select("SELECT " +
            "n.id, n.user_id as userId, n.title, n.content, n.is_private as isPrivate, " +
            "n.cover_image_url as coverImageUrl, n.created_at as createdAt, n.updated_at as updatedAt, " +
            "u.nickname, u.avatar_url as avatarUrl " +
            "FROM notes n " +
            "JOIN users u ON n.user_id = u.id " +
            "WHERE n.id = #{id}")
    NoteWithUserDTO selectById(Integer id);



    @Update("UPDATE notes SET is_private = #{isPrivate}, updated_at = NOW() WHERE id = #{id}")
    int updatePrivacy(@Param("id") Integer id, @Param("isPrivate") Boolean isPrivate);


    @Select({
            "SELECT n.id, n.user_id userId, n.title, n.content, n.is_private isPrivate, ",
            "n.cover_image_url coverImageUrl, n.created_at createdAt, n.updated_at updatedAt, ",
            "u.nickname, u.avatar_url avatarUrl, ",
            "IFNULL(l.cnt, 0) likes, ",
            "IFNULL(c.cnt, 0) collects, ",
            "IFNULL(cm.cnt, 0) comments, ",
            "IFNULL(l.mine, 0) isLiked, ",
            "IFNULL(c.mine, 0) isCollected ",
            "FROM notes n ",
            "JOIN users u ON n.user_id = u.id ",
            "LEFT JOIN ( ",
            "  SELECT note_id, ",
            "         COUNT(*) cnt, ",
            "         MAX(CASE WHEN user_id = #{currentUserId} THEN 1 ELSE 0 END) mine ",
            "  FROM likes GROUP BY note_id ",
            ") l ON l.note_id = n.id ",
            "LEFT JOIN ( ",
            "  SELECT note_id, ",
            "         COUNT(*) cnt, ",
            "         MAX(CASE WHEN user_id = #{currentUserId} THEN 1 ELSE 0 END) mine ",
            "  FROM collections GROUP BY note_id ",
            ") c ON c.note_id = n.id ",
            "LEFT JOIN ( ",
            "  SELECT note_id, COUNT(*) cnt ",
            "  FROM comments GROUP BY note_id ",
            ") cm ON cm.note_id = n.id ",
            "WHERE n.user_id = #{userId} ",
            "ORDER BY n.created_at DESC"
    })
    List<NoteWithStatsVO> selectUserNotesWithStats(
            @Param("userId") Integer userId,
            @Param("currentUserId") Integer currentUserId);


    @Select({
            "SELECT ",
            "n.id, n.user_id userId, n.title, n.content, n.is_private isPrivate, ",
            "n.cover_image_url coverImageUrl, n.created_at createdAt, n.updated_at updatedAt, ",
            "u.nickname, u.avatar_url avatarUrl, ",
            "IFNULL(l.cnt, 0) likes, ",
            "IFNULL(c.cnt, 0) collects, ",
            "IFNULL(cm.cnt, 0) comments, ",
            "IFNULL(l.mine, 0) isLiked, ",
            "IFNULL(c.mine, 0) isCollected ",
            "FROM notes n ",
            "JOIN users u ON n.user_id = u.id ",
            "JOIN note_tags nt ON n.id = nt.note_id AND nt.tag_id = #{tagId} ",
            "LEFT JOIN ( ",
            "  SELECT note_id, ",
            "         COUNT(*) cnt, ",
            "         MAX(CASE WHEN #{userId} IS NOT NULL AND user_id = #{userId} THEN 1 ELSE 0 END) mine ",
            "  FROM likes GROUP BY note_id ",
            ") l ON l.note_id = n.id ",
            "LEFT JOIN ( ",
            "  SELECT note_id, ",
            "         COUNT(*) cnt, ",
            "         MAX(CASE WHEN #{userId} IS NOT NULL AND user_id = #{userId} THEN 1 ELSE 0 END) mine ",
            "  FROM collections GROUP BY note_id ",
            ") c ON c.note_id = n.id ",
            "LEFT JOIN ( ",
            "  SELECT note_id, COUNT(*) cnt ",
            "  FROM comments GROUP BY note_id ",
            ") cm ON cm.note_id = n.id ",
            "WHERE n.is_private = FALSE ",
            "ORDER BY n.created_at DESC LIMIT #{limit}"
    })
    List<NoteWithStatsVO> selectPublicNotesByTagWithStats(
            @Param("tagId") Integer tagId,
            @Param("limit") int limit,
            @Param("userId") Integer userId);



    @Select({
            "SELECT ",
            "n.id, n.user_id userId, n.title, n.content, n.is_private isPrivate, ",
            "n.cover_image_url coverImageUrl, n.created_at createdAt, n.updated_at updatedAt, ",
            "u.nickname, u.avatar_url avatarUrl, ",
            "IFNULL(l.cnt,0) likes, ",
            "IFNULL(c.cnt,0) collects, ",
            "IFNULL(cm.cnt,0) comments, ",
            "IFNULL(l.mine,0) isLiked, ",
            "IFNULL(c.mine,0) isCollected ",
            "FROM notes n ",
            "JOIN users u ON n.user_id = u.id ",
            "LEFT JOIN ( ",
            "  SELECT note_id, ",
            "         COUNT(*) cnt, ",
            "         MAX(CASE WHEN user_id = #{userId} THEN 1 ELSE 0 END) mine ",
            "  FROM likes GROUP BY note_id ",
            ") l ON l.note_id = n.id ",
            "LEFT JOIN ( ",
            "  SELECT note_id, ",
            "         COUNT(*) cnt, ",
            "         MAX(CASE WHEN user_id = #{userId} THEN 1 ELSE 0 END) mine ",
            "  FROM collections GROUP BY note_id ",
            ") c ON c.note_id = n.id ",
            "LEFT JOIN ( ",
            "  SELECT note_id, COUNT(*) cnt ",
            "  FROM comments GROUP BY note_id ",
            ") cm ON cm.note_id = n.id ",
            "WHERE n.is_private = FALSE ",
            "ORDER BY n.created_at DESC LIMIT #{limit}"
    })
    List<NoteWithStatsVO> selectNotesWithStats(@Param("userId") Integer userId, @Param("limit") int limit);





    /* ******* 点赞列表带统计 ******* */
    @Select({
            "SELECT n.id, n.user_id userId, n.title, n.content, n.is_private isPrivate, ",
            "n.cover_image_url coverImageUrl, n.created_at createdAt, n.updated_at updatedAt, ",
            "u.nickname, u.avatar_url avatarUrl, ",
            "IFNULL(l.cnt,0) likes, ",
            "IFNULL(c.cnt,0) collects, ",
            "IFNULL(cm.cnt,0) comments, ",
            "1 isLiked, ",
            "IFNULL(c.mine,0) isCollected ",
            "FROM likes lk ",
            "JOIN notes n ON n.id = lk.note_id ",
            "JOIN users u ON u.id = n.user_id ",
            "LEFT JOIN (SELECT note_id, COUNT(*) cnt FROM likes GROUP BY note_id) l ON l.note_id = n.id ",
            "LEFT JOIN (",
            "  SELECT note_id, ",
            "         COUNT(*) cnt, ",
            "         MAX(CASE WHEN user_id = #{userId} THEN 1 ELSE 0 END) mine ",
            "  FROM collections ",
            "  GROUP BY note_id ",
            ") c ON c.note_id = n.id ",
            "LEFT JOIN (SELECT note_id, COUNT(*) cnt FROM comments GROUP BY note_id) cm ON cm.note_id = n.id ",
            "WHERE lk.user_id = #{userId} AND n.is_private = FALSE ",
            "ORDER BY lk.created_at DESC"
    })
    List<NoteWithStatsVO> selectLikedNotesWithStats(@Param("userId") Integer userId);


    /* ******* 收藏列表带统计 ******* */
    @Select({
            "SELECT n.id, n.user_id userId, n.title, n.content, n.is_private isPrivate, ",
            "n.cover_image_url coverImageUrl, n.created_at createdAt, n.updated_at updatedAt, ",
            "u.nickname, u.avatar_url avatarUrl, ",
            "IFNULL(l.cnt,0) likes, ",
            "IFNULL(c.cnt,0) collects, ",
            "IFNULL(cm.cnt,0) comments, ",
            "IFNULL(l.mine,0) isLiked, ",
            "1 isCollected ",
            "FROM collections col ",
            "JOIN notes n ON n.id = col.note_id ",
            "JOIN users u ON u.id = n.user_id ",
            "LEFT JOIN (",
            "  SELECT note_id, ",
            "         COUNT(*) cnt, ",
            "         MAX(CASE WHEN user_id = #{userId} THEN 1 ELSE 0 END) mine ",
            "  FROM likes ",
            "  GROUP BY note_id ",
            ") l ON l.note_id = n.id ",
            "LEFT JOIN (SELECT note_id, COUNT(*) cnt FROM collections GROUP BY note_id) c ON c.note_id = n.id ",
            "LEFT JOIN (SELECT note_id, COUNT(*) cnt FROM comments GROUP BY note_id) cm ON cm.note_id = n.id ",
            "WHERE col.user_id = #{userId} AND n.is_private = FALSE ",
            "ORDER BY col.created_at DESC"
    })
    List<NoteWithStatsVO> selectCollectedNotesWithStats(@Param("userId") Integer userId);
}